"""
Case Type   : spq多机并行
Case Name   : spq插件使能测试：postgresql.conf中不配置spqplugin.cluster_map,查询
Create At   : 2024/04/08
Owner       : songjing20
Description :
    1.配置spq参数，不配置spqplugin.cluster_map，重启集群
    2.建表，预置数据
    3.查询参数和查询表执行计划
    4.使用set设置spqplugin.cluster_map后再次查询表和执行计划
    5.清理环境
Expect      :
    1.成功
    2.成功
    3.空,原生流程
    4.成功,多级并行
    5.成功
History     :
"""

import os
import unittest

from yat.test import macro
from yat.test import Node
from testcase.utils.Logger import Logger
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant

Primary_SH = CommonSH('PrimaryDbUser')


@unittest.skipIf(3 > Primary_SH.get_node_num(), '非1+2以上环境不执行')
class Pubsub(unittest.TestCase):
    def setUp(self):
        self.log = Logger()
        self.log.info(f"-----{os.path.basename(__file__)} start-----")
        self.pri_node = Node(node='PrimaryDbUser')
        self.std1_node = Node(node='Standby1DbUser')
        self.std2_node = Node(node='Standby2DbUser')
        self.commsh_std1 = CommonSH('Standby1DbUser')
        self.commsh_std2 = CommonSH('Standby2DbUser')
        self.constant = Constant()
        self.table = 't_spq_0017'
        self.port = self.pri_node.db_port
        self.pgxc_node = {Primary_SH: 'dn_6001',
                          self.commsh_std1: 'dn_6002',
                          self.commsh_std2: 'dn_6003'}
        self.cluster_map = f'dn_6001|{self.pri_node.db_host}|{self.port},' \
                           f'dn_6002|{self.std1_node.db_host}|{self.port},' \
                           f'dn_6003|{self.std2_node.db_host}|{self.port}'
        self.log.info('----- 集群状态 -----')
        status = Primary_SH.exec_cm_ctl('query', '-Cv')
        self.log.info(status)
        self.assertIn('cluster_state   : Normal', status, f'集群状态异常')

    def test_pubsub(self):
        text = '-----step1:配置spq参数，不配置spqplugin.cluster_map，重启集群;' \
               'expect:成功-----'
        for node, name in self.pgxc_node.items():
            guc_res = node.execute_gsguc(
                'set', self.constant.GSGUC_SUCCESS_MSG,
                f"pgxc_node_name = '{name}'", single=True)
            self.assertTrue(guc_res, f'设置pgxc_node_name失败:{text}')

        cmd = f"echo 'spqplugin.enable_spq=on' >> " \
              f"{macro.DB_INSTANCE_PATH}/postgresql.conf"
        res = Primary_SH.exec_gs_ssh(cmd)
        self.log.info(res)
        self.assertEqual(res.count('[SUCCESS]'), 3, f'执行失败:{text}')
        restart_res = Primary_SH.restart_db_cluster()
        self.assertTrue(restart_res, f'重启集群失败:{text}')

        text = '-----step2:建表，预置数据;expect:成功-----'
        self.log.info(text)
        res = Primary_SH.execut_db_sql(
            f'''create type xb as enum('男','女','保密');
            create table {self.table}(
            id        int primary key,
            name      varchar(20) default '',
            age       tinyint default 0,
            height    decimal(5,2),
            gender    xb default '保密',
            cls_id    int default 0,
            is_delete bit default '0');
            insert into {self.table} values(
            1, 'LiMing', 12, 1.58, '男', 1, false);''')
        self.log.info(res)
        self.assertIn(self.constant.CREATE_TABLE_SUCCESS,
                      res, f'执行失败:{text}')
        self.assertIn(self.constant.INSERT_SUCCESS_MSG,
                      res, f'执行失败:{text}')

        text = '-----step3:查询参数和查询表执行计划;expect:空,原生流程-----'
        self.log.info(text)
        res = Primary_SH.execut_db_sql(
            f"show spqplugin.cluster_map;"
            f"explain select * from students;")
        self.log.info(res)
        self.assertIn('(0 rows)', res, f'执行失败:{text}')
        self.assertNotIn('Spq seq scan', res, f'执行失败:{text}')

        text = '-----step4:使用set设置spqplugin.cluster_map后再次查询表和执行计划;' \
               'expect:成功，多级并行-----'
        res = Primary_SH.execut_db_sql(
            f"set spqplugin.cluster_map={self.cluster_map};"
            f"show spqplugin.enable_spq;"
            f"explain select * from students;")
        self.log.info(res)
        self.assertIn(f'{self.cluster_map}\n(1 row)', res, f'执行失败:{text}')
        self.assertIn('Spq seq scan', res, f'执行失败:{text}')

    def tearDown(self):
        text = '-----step5:恢复环境;expect:成功-----'
        self.log.info(text)
        drop_res = Primary_SH.execut_db_sql(
            f'drop table {self.table} cascade;drop type xb cascade;')
        self.log.info(drop_res)
        self.log.info('----- 恢复postgresql.conf -----')
        guc_res1 = Primary_SH.execute_gsguc(
            'set', self.constant.GSGUC_SUCCESS_MSG,
            "shared_preload_libraries = 'security_plugin'")
        guc_res2 = Primary_SH.execute_gsguc(
            'set', self.constant.GSGUC_SUCCESS_MSG,
            "pgxc_node_name = 'dn_6001_6002_6003'")
        cmd = "sed -i '/spqplugin.cluster_map/d' $PGDATA/postgresql.conf"
        res = Primary_SH.exec_gs_ssh(cmd)
        self.log.info(res)
        restart_res = Primary_SH.restart_db_cluster()
        self.log.info(restart_res)
        self.assertIn(self.constant.DROP_TYPE_SUCCESS_MSG, drop_res,
                      f'执行失败:{text}')
        self.assertIn(self.constant.DROP_TABLE_SUCCESS,
                      drop_res, f'执行失败:{text}')
        self.assertTrue(guc_res1 and guc_res2, f'执行失败:{text}')
        self.assertIn('Successfully execute command on all nodes.',
                      res, f'执行失败:{text}')
        self.log.info(f"-----{os.path.basename(__file__)} end-----")
